# -*- coding: utf-8 -*-

#
# 每日检查过了deadline的考试，计算小组排名，给前三名发放积分奖励
#
import mysql.connector
from mysql.connector import Error
import logging
from typing import List, Dict, Tuple
from datetime import datetime
import logging
import sys

# 配置日志（同时输出到文件和控制台）
logger = logging.getLogger()
logger.setLevel(logging.INFO)  # 设置全局日志级别

# 定义日志格式
formatter = logging.Formatter(
    '%(asctime)s - %(levelname)s - %(message)s'
)

# 文件日志处理器（写入文件）
file_handler = logging.FileHandler(f'exam_reward_{datetime.now().strftime('%Y-%m-%d-%H-%M-%S')}.log', encoding='utf-8')
file_handler.setFormatter(formatter)

# 控制台日志处理器（输出到控制台）
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(formatter)

# 给logger添加处理器
logger.addHandler(file_handler)
logger.addHandler(console_handler)

# 配置参数（请根据实际环境修改）
CONFIG = {
    # 数据库连接信息
    "db_host": "localhost",
    "db_user": "root",
    "db_password": "root",
    "db_name": "school_class",
    # 加分规则：排名 -> 加分值
    "reward_rules": {
        1: 3,
        2: 2,
        3: 1
    }
}


def get_qualified_exams(cursor, current_time) -> List[Dict]:
    """查询符合条件的考试（given!=1且deadline<当前时间）"""
    query = """
    SELECT id, name 
    FROM exams 
    WHERE (given is null or given != 1) AND deadline < %s
    """
    cursor.execute(query, (current_time,))
    exams = cursor.fetchall()
    return [{"exam_id": e[0], "exam_name": e[1]} for e in exams]


def get_group_scores(cursor, exam_id) -> Dict[int, Tuple[str, float]]:
    """获取指定考试中各小组的平均分（排除无成绩的小组）"""
    query = """
    SELECT 
        g.id AS group_id,
        g.name AS group_name,
        AVG(s.score) AS avg_score
    FROM scores s
    JOIN students st ON s.student_id = st.id
    JOIN study_groups g ON st.group_id = g.id
    WHERE s.exam_id = %s
    GROUP BY g.id, g.name
    HAVING COUNT(s.student_id) > 0  # 排除无成绩的小组（无学生参与考试）
    """
    cursor.execute(query, (exam_id,))
    results = cursor.fetchall()
    # 返回 {group_id: (group_name, avg_score)}
    return {
        res[0]: (res[1], res[2]) 
        for res in results
    }


def get_ranked_groups(group_scores: Dict) -> List[Tuple[int, str, int]]:
    """计算所有小组的排名（处理并列），返回排名≤3的小组"""
    if not group_scores:
        return []
    
    # 按平均分降序排序（分数相同则按group_id升序，保证排序稳定性）
    sorted_groups = sorted(
        group_scores.items(),
        key=lambda x: (-x[1][1], x[0])  # 负号表示降序，group_id为次要键
    )
    
    ranked_groups = []
    if not sorted_groups:
        return ranked_groups
    
    # 初始化第一个小组的排名和分数
    current_rank = 1
    prev_score = sorted_groups[0][1][1]  # 第一个小组的平均分
    
    for index, (group_id, (group_name, avg_score)) in enumerate(sorted_groups):
        # 处理并列排名：分数与前一名不同时，更新排名为当前索引+1（索引从0开始）
        if index > 0:
            if avg_score != prev_score:
                current_rank = index + 1  # 排名从1开始计数
                prev_score = avg_score
        
        # 只保留排名≤3的小组，超出则终止遍历
        if current_rank > 3:
            break
        
        ranked_groups.append((group_id, group_name, current_rank))
    
    return ranked_groups


def get_group_students(cursor, group_id) -> List[int]:
    """获取指定小组的所有学生ID"""
    query = "SELECT id FROM students WHERE group_id = %s"
    cursor.execute(query, (group_id,))
    return [row[0] for row in cursor.fetchall()]


def reward_students(
    cursor, 
    student_ids: List[int], 
    points: int, 
    exam_name: str, 
    rank: int
) -> None:
    """为学生加分并记录到积分日志"""
    # 1. 更新学生的performance_points
    update_query = """
    UPDATE students 
    SET performance_points = performance_points + %s 
    WHERE id = %s
    """
    student_updates = [(points, sid) for sid in student_ids]
    cursor.executemany(update_query, student_updates)
    
    # 2. 记录积分变动（当前时间作为记录时间）
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    reason = f"在{exam_name}中小组获得第{rank}名"
    insert_query = """
    INSERT INTO points_records 
    (teacher_id, student_id, points_change, reason, created_at) 
    VALUES (1, %s, %s, %s, %s)
    """
    records = [(sid, points, reason, now) for sid in student_ids]
    cursor.executemany(insert_query, records)


def process_exam(cursor, exam: Dict, reward_rules: Dict) -> None:
    """处理单个考试的加分流程（含并列排名处理）"""
    exam_id = exam["exam_id"]
    exam_name = exam["exam_name"]
    logging.info(f"开始处理考试：{exam_name}（ID：{exam_id}）")

    # 1. 获取各小组在该考试中的平均分（排除无成绩的小组）
    group_scores = get_group_scores(cursor, exam_id)
    if not group_scores:
        logging.warning(f"考试{exam_name}无有效成绩数据（所有小组均无成绩），跳过处理")
        return

    # 2. 计算排名（处理并列），获取所有排名≤3的小组
    ranked_groups = get_ranked_groups(group_scores)
    if not ranked_groups:
        logging.warning(f"考试{exam_name}所有小组排名均>3，无需加分")
        return

    # 3. 为排名≤3的小组学生加分
    # 按排名分组统计，便于日志记录
    rank_summary = {}
    for group_id, group_name, rank in ranked_groups:
        points = reward_rules.get(rank, 0)
        if points <= 0:
            continue
        
        # 获取小组所有学生
        students = get_group_students(cursor, group_id)
        if not students:
            logging.warning(f"小组{group_name}（ID：{group_id}）无学生，跳过加分")
            continue
        
        # 加分并记录
        reward_students(cursor, students, points, exam_name, rank)
        
        # 更新统计信息
        if rank not in rank_summary:
            rank_summary[rank] = {"count": 0, "total_students": 0}
        rank_summary[rank]["count"] += 1
        rank_summary[rank]["total_students"] += len(students)

    # 记录排名统计日志
    for rank in sorted(rank_summary.keys()):
        summary = rank_summary[rank]
        logging.info(
            f"考试{exam_name}：第{rank}名共{summary['count']}个小组，"
            f"合计{summary['total_students']}名学生，每人加{reward_rules[rank]}分"
        )

    # 4. 标记考试为已处理（given=1）
    cursor.execute("UPDATE exams SET given = 1 WHERE id = %s", (exam_id,))
    logging.info(f"考试{exam_name}处理完成，已标记为已处理")


def main():
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")  # 当前时间字符串
    db = None
    cursor = None

    try:
        # 连接数据库
        db = mysql.connector.connect(
            host=CONFIG["db_host"],
            user=CONFIG["db_user"],
            password=CONFIG["db_password"],
            database=CONFIG["db_name"]
        )
        if not db.is_connected():
            raise Error("数据库连接失败")
        cursor = db.cursor(dictionary=False)

        # 获取所有符合条件的考试
        qualified_exams = get_qualified_exams(cursor, current_time)
        if not qualified_exams:
            logging.info("没有需要处理的考试（无符合条件的未处理过期考试），程序退出")
            print("没有需要处理的考试")
            return

        # 逐个处理考试（每个考试一个事务，确保原子性）
        for exam in qualified_exams:
            try:
                process_exam(cursor, exam, CONFIG["reward_rules"])
                db.commit()
            except Exception as e:
                db.rollback()
                error_msg = f"处理考试{exam['exam_name']}失败：{str(e)}"
                logging.error(error_msg)
                print(error_msg)
                continue

        print("所有符合条件的考试已处理完成")

    except Error as e:
        error_msg = f"数据库连接错误：{str(e)}"
        logging.error(error_msg)
        print(error_msg)
    except Exception as e:
        error_msg = f"程序运行错误：{str(e)}"
        logging.error(error_msg)
        print(error_msg)
    finally:
        # 关闭资源
        if cursor:
            cursor.close()
        if db and db.is_connected():
            db.close()
        logging.info("数据库连接已关闭")


if __name__ == "__main__":
    main()